import pymysql
import time
import traceback
import requests
import json

from selenium.webdriver import Chrome, ChromeOptions


def get_tencent_data():
    url_det = 'https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=diseaseh5Shelf'
    url_his = ("https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=chinaDayList,"
               "chinaDayAddList,nowConfirmStatis,provinceCompare")
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/79.0.3945.88 Safari/537.36',
    }
    r_det = requests.get(url_det, headers)
    r_his = requests.get(url_his, headers)
    res_det = json.loads(r_det.text)  # json字符串转字典
    res_his = json.loads(r_his.text)
    data_det = res_det['data']['diseaseh5Shelf']
    data_his = res_his['data']

    history = {}

    for i in data_his['chinaDayList']:
        ds = i['y'] + '.' + i['date']
        tup = time.strptime(ds, '%Y.%m.%d')
        ds = time.strftime('%Y-%m-%d', tup)
        history[ds] = {'confirm': i['confirm'],
                       'suspect': i['suspect'],
                       'heal': i['heal'], 'dead': i['dead']}

    for i in data_his['chinaDayAddList']:
        ds = i['y'] + '.' + i['date']
        tup = time.strptime(ds, '%Y.%m.%d')
        ds = time.strftime('%Y-%m-%d', tup)
        if ds not in history.keys():
            continue
        history[ds].update({'confirm_add': i['confirm'],
                            'suspect_add': i['suspect'],
                            'heal_add': i['heal'], 'dead_add': i['dead']})

    details = []
    update_time = data_det['lastUpdateTime']
    data_province = data_det['areaTree'][0]['children']
    for pro_infos in data_province:
        province = pro_infos['name']
        for city_infos in pro_infos['children']:
            city = city_infos['name']
            confirm = city_infos['total']['confirm']
            confirm_add = city_infos['today']['confirm']
            heal = city_infos['total']['heal']
            dead = city_infos['total']['dead']
            details.append([update_time, province, city, confirm,
                            confirm_add, heal, dead])
    return {'history': history, 'details': details}


def insert_history(data: dict):
    global cursor
    try:
        print(f'{time.asctime()} 开始插入数据')
        cursor = db.cursor()
        for k, v in data.items():
            sql_query = f"insert into history values('{k}',{v['confirm']},{v['confirm_add']}," \
                        f"{v['suspect']},{v['suspect_add']},{v['heal']},{v['heal_add']}," \
                        f"{v['dead']},{v['dead_add']})"
            print(sql_query)
            cursor.execute(sql_query)
        db.commit()
        print(f'{time.asctime()} 完成插入数据')
    except:
        traceback.print_exc()
    finally:
        cursor.close()


def update_history(data: dict):
    try:
        print(f'{time.asctime()} 开始更新历史数据')
        cursor = db.cursor()
        sql = 'select confirm from history where ds=%s'
        for k, v in data.items():
            if len(v.keys()) != 8:
                continue
            if not cursor.execute(sql, k):
                sql_query = f"insert into history values('{k}',{v['confirm']},{v['confirm_add']}," \
                            f"{v['suspect']},{v['suspect_add']},{v['heal']},{v['heal_add']}," \
                            f"{v['dead']},{v['dead_add']})"
                cursor.execute(sql_query)
        db.commit()
        print(f'{time.asctime()} 完成更新历史数据')
    except:
        traceback.print_exc()
    finally:
        if 'cursor' in locals().keys():
            cursor.close()


# 定义一个函数，用于更新details表中的数据
def update_details(data: list):
    # 定义一个游标
    cursor = None
    try:
        # 获取游标
        cursor = db.cursor()
        # 子查询，选中update_time字段，按照id字段的降序排列顺序，选出update_time字段第一个
        # 将返回的时间与我们传入的时间比较，相同返回1
        sql = 'select %s=(select update_time from details order by id desc limit 1)'
        # 指定插入顺序
        sql_query = f"insert into details (update_time,province,city,confirm,confirm_add,"                     f"heal,dead) values(%s,%s,%s,%s,%s,%s,%s)"
        # print(data[0][0])
        # 执行子查询，获取最大时间戳
        cursor.execute(sql, data[0][0])  # 对比最大时间戳
        # 获取查询结果
        result = cursor.fetchone()[0]
        # 判断查询结果
        if not result:
            print(f'{time.asctime()} 开始更新数据')
            # 执行插入操作
            for item in data:
                cursor.execute(sql_query, item)
            # 提交操作
            db.commit()
            print(f'{time.asctime()} 完成更新数据')
        else:
            print(f'{time.asctime()} 已是最新数据')
    except:
        # 打印异常信息
        traceback.print_exc()
    finally:
        # 关闭游标
        if cursor:
            cursor.close()


# 定义一个函数getBaiduData，用于获取百度热搜数据
def getBaiduData():
    # 创建一个ChromeOptions对象，用于设置浏览器参数
    option = ChromeOptions()
    option.add_argument('--headless')  # 隐藏浏览器
    option.add_argument('--no-sandbox')  # linux下需要添加

    # 创建一个浏览器对象，并设置参数
    browser = Chrome(options=option)
    # 设置爬取的网页地址
    url = 'https://top.baidu.com/board?tab=realtime'
    # 使用浏览器打开网页
    browser.get(url)

    # 设置xpath，用于爬取网页数据
    xpath = '//*[@id="sanRoot"]/main/div[2]/div/div[2]/div/div[2]/a/div[1]'
    # 使用浏览器爬取数据
    elements = browser.find_elements(by='xpath', value=xpath)
    # 将爬取的数据存入content列表中
    content = [element.text for element in elements]
    # 关闭浏览器
    browser.close()
    # 返回爬取的数据
    return content


# 定义更新热搜函数
def updateHotSearch():
    # 创建游标
    cursor = None
    try:
        # 获取百度数据
        content = getBaiduData()
        print(f'{time.asctime()} 开始更新热搜数据')
        # 创建游标
        cursor = db.cursor()
        # 插入数据sql语句
        sql = 'insert into hotsearch (dt,content) values(%s,%s)'
        # 获取当前时间
        ts = time.strftime('%Y-%m-%d %X')
        # 遍历数据
        for line in content:
            # 执行插入数据sql语句
            cursor.execute(sql, (ts, line))
        # 提交数据
        db.commit()
        print(f'{time.asctime()} 热搜数据更新完毕')
    except:
        # 打印异常信息
        traceback.print_exc()
    finally:
        # 关闭游标
        if cursor:
            cursor.close()


# 连接数据库
db = pymysql.connect(host="127.0.0.1", port=3306,
                     user="root",
                     password="root",
                     db="sys",
                     charset="utf8")
# 获取腾讯数据
data = get_tencent_data()

# 更新历史数据
update_history(data['history'])
# 更新详细数据
update_details(data['details'])
# 更新热搜数据
updateHotSearch()
# 关闭数据库连接
db.close()
